This is a central index of all the Excel Tips & Tricks and Tip of the Week posts. They are grouped together by topic, and then by level in the Spreadsheet Competency Framework.
Latest posts
Formulas
Tips 1 - 100
- 2 - TRIM and CLEAN
- 7 - VLOOKUP
- 8 - Comparisons
- 10 - UPPER, LOWER, PROPER
- 11 - Formulas with fixed references
- 14 - CONCATENATE
- 15 - AND, OR
- 17 - IF and nested IFs
- 21 - LEFT, RIGHT, MID
- 23 - Evaluate Formula and formula auditing
- 26 - LEN, FIND, SEARCH
- 30 - Reviewing formulas
- 32 - Naming cells
- 34 - ADDRESS, INDIRECT
- 35 - SUM, COUNT, COUNTA, COUNTBLANK
- 37 - Creating error checks
- 42 - ROUND, ROUNDDOWN, ROUNDUP
- 43 - SUMIF, COUNTIF, SUMIFS, COUNTIFS
- 45 - FLOOR, CEILING, MROUND
- 46 - ABS
- 47 - NETWORKDAYS, WORKDAY
- 49 - Working with dates
- 50 - INDEX MATCH
- 53 - Causes of formulas not calculating
- 55 - Finding a function that fits
- 56 - SUMPRODUCT
- 57 - RAND, RANDBETWEEN
- 60 - DATE, EDATE, EOMONTH
- 61 - TODAY, NOW
- 63 - ROW, COLUMN, ROWS, COLUMNS
- 66 - IF revisited
- 68 - MAX, MIN, LARGE, SMALL
- 69 - HYPERLINK
- 70 - Revisiting CONCATENATE
- 74 - Revisiting formulas with fixed references
- 78 - Revisiting COUNTIF, SUMIF, COUNTIFS, SUMIFS
- 85 - NPV, XNPV, IRR, XIRR
- 88 - SUBSTITUTE
- 89 - NETWORKDAYS.INTL, WORKDAY.INTL
- 90 - DATEDIF
- 91 - Revisiting VLOOKUP
- 92 - Revisiting SUM, COUNT, COUNTA, COUNTBLANK
- 93 - AutoSum
- 95 - Revisiting naming cells and the Name Manager
- 96 - CHOOSE
- 99 - Introduction to array formulas
- 100 - Working with arrays
Tips 101 - 200
- 103 - Loan-related functions
- 104 - N, T
- 106 - CELL, INFO
- 107 - Formula Wizard
- 109 - Selecting a scenario
- 112 - OFFSET
- 117 - Revisiting INDEX MATCH
- 122 - Formatting using formulas
- 127 - AND / OR revisited
- 131 - Revisiting MAX, MIN, LARGE, SMALL
- 132 - Revisiting ADDRESS, INDIRECT
- 133 - "IS" functions
- 137 - R1C1 reference style
- 138 - When will I get paid?
- 142 - How many distinct items?
- 145 - RANK.AVG, RANK.EQ
- 146 - Revisiting RAND, RANDBETWEEN
- 151 - SUBTOTAL, AGGREGATE
- 152 - Nesting functions in the Formula Wizard
- 154 - Database functions
- 155 - Using INDEX MATCH with multiple variables
- 158 - Revisiting formulas that aren't calculating
- 161 - Revisiting text extraction functions
- 162 - Normal distribution functions
- 165 - FORMULATEXT
- 168 - VLOOKUP redux
- 169 - Revisiting NPV, XNPV, IRR, XIRR
- 172 - Rotating between values
- 176 - Basic statistics functions
- 179 - GETPIVOTDATA
- 182 - Revisiting array formulas
- 183 - FREQUENCY
- 188 - Named cells redux
- 189 - Revisiting CHOOSE
- 197 - WEEKDAY
- 198 - Checking formulas in the formula bar
- 200 - Fixed references redux
Tips 201 - 300
- 201 - INDEX MATCH redux
- 204 - Count items in common
- 206 - CONVERT
- 207 - Revisiting SUBSTITUTE
- 208 - Revisiting TODAY, NOW
- 210 - Switching bases
- 211 - Sorting with formulas
- 212 - Revisiting TRIM
- 213 - CODE, CHAR, UNICODE, UNICHAR
- 224 - Revisiting SUMPRODUCT
- 234 - Inserting a calculated value into a sentence
- 236 - Creating numeric patterns
- 238 - Revisiting working with dates
- 245 - Finding the last number in a range
- 246 - Modelling a simple loan
- 247 - Causes of formulas not working
- 249 - Revisiting making dates with formulas
- 252 - Revisiting rounding
- 253 - Calculating the effective interest rate
- 254 - Rounding with totals
- 259 - CONCATENATE redux
- 261 - Revisiting AutoSum
- 264 - Working day functions revisited
- 265 - Revisiting DATEDIF
- 267 - Creating dynamic ranges
- 268 - Using quotation marks in formulas
- 272 - Revisiting finding the function you need
- 273 - IF redux
- 275 - Cross-sheet formulas
- 279 - Revisiting N, T
- 280 - SUM and COUNT functions redux
- 281 - Revisiting CELL and INFO
- 282 - Spaces in formulas
- 288 - Revisiting database functions
- 294 - Converting data stored as text
- 295 - Binomial distribution functions
- 298 - Formula issues redux
- 299 - Google Sheets: UNIQUE
Tips 301 - 400
- 302 - Linear regression functions
- 303 - Revisiting the Formula Wizard
- 304 - Google Sheets: FILTER
- 305 - Revisiting counting distinct items
- 308 - Google Sheets: QUERY
- 309 - Randomisation redux
- 310 - Revisiting reviewing formulas
- 314 - ADDRESS and INDIRECT redux
- 315 - Google Sheets: Counting distinct items
- 316 - Conditional summary functions redux
- 317 - Basic arithmetic functions
- 319 - Revisiting selecting a scenario
- 320 - Revisiting OFFSET
- 322 - Quartiles and percentiles
- 324 - Introduction to formulas and functions
- 326 - Revisiting loan functions
- 327 - Introduction to dynamic array functions
- 329 - CONCAT, TEXTJOIN
- 330 - IFS, SWITCH
- 333 - Revisiting GETPIVOTDATA
- 334 - Maximum and minimum functions redux
- 336 - Why is there an @ in my Excel formula?
- 337 - XLOOKUP
- 340 - Calculating tiered percentages
- 341 - Text extraction functions redux
- 342 - Revisiting HYPERLINK
- 343 - SUMPRODUCT redux
- 345 - Spilling errors with dynamic arrays
- 346 - Revisiting case-changing functions
- 349 - Rounding redux
- 352 - Keyboard shortcuts for entering formulas
- 353 - Audit sampling templates: Dynamic arrays
- 354 - REPLACE
- 356 - Audit sampling templates: Traditional formulas
- 358 - Revisiting FREQUENCY
- 371 - Revisiting ROW(S) and COLUMN(S) functions
- 372 - Creating a depreciation schedule
- 373 - The 12 formulas of Christmas
- 376 - Discounting functions redux
- 383 - Revisiting ranking functions
- 390 - Advanced uses of INDEX
- 391 - Comparing the lookup functions
- 393 - AND/OR redux
- 396 - Revisiting showing formulas as text
Tips 401 - 500
- 405 - DATEDIF redux
- 407 - Ranking within a group
- 422 - Better error catching formulas
- 429 - Revisiting cross-sheet formulas
- 431 - SUBSTITUTE redux
- 432 - Date-creating formulas redux
- 435 - Working with times
- 436 - Trimming excess spaces redux!
- 437 - Revisiting dynamic arrays
- 443 - Next level FILTERing (The FILTER Function)
- 444 - Revisiting XLOOKUP
- 449 - Let LET simplify formulas
- 450 - Revisiting Custom Data Validation
- 452 - Named Ranges Refreshed
- 459 - Examples of using LAMBDA() in Excel
- 462 - New text manipulation functions - TEXTBEFORE and TEXTAFTER
- 467 - Excel functions that do not return arrays or ranges
- 471 - Refreshing GETPIVOTDATA
- 474 - How to REDUCE repetitive calculations to a single step
- 476 - Cross join in Excel using a formula
- 485 - Refreshing NETWORKDAYS and WORKDAY functions
- 491 - Creating a dynamic lead schedule
Formatting & appearance
Tips 1 - 100
- 3 - Introduction to conditional formatting
- 9 - Dropdowns
- 16 - Data validation
- 19 - Paste values
- 41 - Precision as displayed
- 52 - Quick formatting
- 59 - Formatting and text formats
- 65 - Merge, Wrap, Autofit
- 71 - Introduction to charts
- 73 - Revisiting conditional formatting
- 76 - Quick charts and chart tabs
- 79 - Revisiting Tables
- 87 - Sparklines
Tips 101 - 200
- 101 - Form controls
- 108 - Revisiting Paste Special
- 114 - Revisiting data validation & creating dropdown menus
- 115 - Style Manager
- 125 - Too many cell formats
- 126 - Transposing data
- 129 - Hide gridlines
- 135 - Why merging cells sucks
- 136 - Quick formatting for formulas, inputs, and blanks
- 139 - Flipping the default direction
- 143 - Conditional formatting: Colouring a whole row
- 150 - Adding leading zeroes
- 153 - Conditional formatting: Layering multiple formats
- 159 - Setting permissions for different ranges
- 171 - Using #N/A in charts
- 177 - Formula-driven data validation
- 185 - Revisiting sparklines
- 192 - Hidden rows and columns
Tips 201 - 300
- 214 - Line breaks in Excel
- 219 - Nested dropdowns
- 220 - Revisiting cell styles
- 227 - Making text fit
- 228 - Paste Special redux
- 233 - Why merging cells still sucks
- 241 - Cell borders
- 244 - Revisiting Precision as Displayed
- 251 - Cross-workbook Cell Styles
- 269 - Revisiting form controls
- 274 - Mixing multiple chart types
- 276 - Conditional formatting redux
- 283 - Presenting text in a spreadsheet
- 290 - Sparklines redux
- 293 - Revisiting layering conditional formats
- 297 - Revisiting using #N/A! in charts
Tips 301 - 400
- 311 - Less-known formatting options
- 325 - Revisiting quick formatting
- 332 - Revisiting number formatting
- 335 - Dealing with objects
- 360 -Revisiting typing special characters
- 381 -Treemap & Sunburst charts
- 395 - Revisiting hidden rows and columns
Tips 401 - 500
- 416 - PivotTable layouts
- 430 - 3D maps
- 442 - Revisiting screenshots
- 458 - Revisiting line breaks in Excel
- 472 - Conditional formatting components
- 473 – Refreshing customising the ribbon
- 477 - Introduction to creating map charts in Excel
- 481 - Refreshing cell styles
- 488 - The basics of charts in Excel
Data handling & analysis
Tips 1 - 100
- 1 - Comparing lists
- 4 - Filters
- 6 - Tables (basics)
- 13 - Tables (further learning)
- 22 - Data tables
- 24 - Remove duplicates
- 28 - Text to columns
- 29 - PivotTables (basics)
- 36 - PivotTables (further learning)
- 40 - Goal Seek
- 62 - Group, ungroup, subtotal
- 64 - Revisiting PivotTables
- 77 - Revisiting Filters
- 84 - Scenario Manager
- 94 - Find & Replace
- 97 - Working with imported data
- 98 - Revisiting removing duplicates
Tips 101 - 200
- 105 - Revisiting Text to Columns
- 113 - Revisiting Goal Seek
- 116 - Solver Add-In
- 119 - Revisiting Data Tables
- 120 - Revisiting comparing lists
- 134 - Making Benford's Law in Excel
- 148 - Advanced Filter
- 160 - Revisiting the subtotal feature
- 163 - Tables redux, part 1
- 164 - Tables redux, part 2
- 175 - The Excel Data Model
- 190 - PivotTables redux
- 196 - Splitting data by category
Tips 201 - 300
- 203 - Consolidate
- 218 - Data tables redux
- 225 - Finding items that add to a target
- 232 - Analysing frequency tables
- 239 - How to standardise data
- 250 - Goal Seek redux
- 255 - Power Query: Combining multiple tables
- 256 - Power Query: Three-way matching
- 257 - Revisiting the Solver Add-In
- 278 - Using data tables for Monte Carlo simulations
- 285 - FIFO and LIFO templates
- 287 - Revisiting Advanced Filter
- 289 - Floating point errors
- 291 - Making a set of PivotTables
- 292 - Wildcards
- 296 - Data validation redux
Tips 301 - 400
- 318 - Power Query: Unpivoting
- 323 - Creating a trial balance from accounting data
- 339 - Power Query: Filtering for a date range
- 344 - Power Query: Getting started with web queries
- 348 - Fixing badly formatted dates
- 350 - Power Query: Creating a ranking
- 351 - Non-linear regression
- 355 - Connecting Google Sheets and Excel
- 357 - Removing duplicates redux
- 363 - Subtotal feature redux
- 364 - Dynamic arrays: Creating a visual work schedule
- 365 - Revisiting finding items that add to a target
- 367 - PivotCharts
- 368 - Power Pivot: Connections
- 369 - Power Pivot: Measures
- 378 - Revisiting splitting data by category
- 379 - Processing survey results
- 384 - Data types
- 385 - Power Query: Creating transaction references
- 386 - Power Pivot - RELATEDTABLE
- 387 - Revisiting fixing broken links
- 388 - Power Pivot: Filtering measures
- 389 - Power Query Dropping Rows and Columns
- 392 - Text to Columns redux
- 397 - Power Query - Looking up charge-out rates
- 399 - Power pivot - KPIs
Tips 401 - 500
- 403 - Power Query: Merge Kinds
- 406 - Power Pivot: Filtering active contracts
- 413 - Flattening and unfolding data
- 417 - Custom data types
- 420 - STOCKHISTORY and exchange rates
- 421 - Comparing lists redux
- 424 - Filters redux
- 425 - Revisiting unpivoting
- 427 - Power Query: Word frequency analysis
- 428 - Converting between statistical measures
- 434 - Revisiting making sets of PivotTables
- 439 - Power Pivot: Create a running total
- 448 - A distinct count conundrum
- 451 - Sparklines refreshed
- 455 - Data conversion
- 456 - Revisting loan modelling
- 479 - Advanced Filter redux
- 486 - Revisiting inserting hyperlinks
Shortcuts & efficiency of use
Tips 1 - 100
- 5 - Quick navigation shortcuts
- 12 - Shortcuts for copying and pasting
- 18 - View shortcuts
- 25 - Data entry shortcuts
- 58 - General efficiency tips
- 72 - Copy and paste errors
- 83 - Revisiting navigation shortcuts
Tips 101 - 200
- 102 - Go To & Go To Special
- 110 - Grouping sheets
- 123 - Improving workbook viewing
- 124 - Flash fill
- 149 - Shortcuts: Working with rows and columns
- 181 - More fill options
- 186 - Quick navigation: Precedents
- 199 - Zooming
Tips 201 - 300
- 202 - Inserting hyperlinks
- 205 - Deletion vs clearing
- 221 - AutoCorrect
- 226 - Breaking links
- 229 - Alt key shortcutting
- 235 - Shortcuts from a financial modeller
- 266 - Keyboard shortcuts advent calendar
- 270 - Function key shortcuts
- 284 - Revisiting Go To and Go To Special
- 286 - Revisiting grouping ranges
- 300 - Copying worksheet tabs
Tips 301 - 400
- 313 - Revisiting find & replace
- 328 - Navigation shortcuts redux
- 361 - How to do data entry
- 362 - The Status Bar
- 375 - Workbook viewing redux
- 380 - AutoSum redux
Tips 401 - 500
- 411 - Revisiting Flash fill
- 412 - Working with large numbers of sheets
- 438 - Power Query: Creating custom functions
- 446 - Desktop vs Online
- 453 - Revisiting audit sampling with dynamic arrays
- 461 - Things Google Sheets (still) does better than Excel
- 478 - Introduction to Power Automate
- 490 - Revisiting data entry shortcuts
- 495 - Excel “Tick”ery!
VBA & macros
Tips 1 - 100
- 33 - Introduction to macros
- 81 - How to install macros and custom functions
- 82 - Useful macros and custom functions
Tips 101 - 200
- 121 - Further dropdown menu approaches
- 141 - VBA case study - SDLT
- *144 - VBA case study - Factor and loops
- 147 - VBA case study - StringConcat and 'For Each'
- *166 - VBA Case Study: Creating scenarios
- 174 - VBA Case Study: Timestamps
- 178 - VBA Case Study: Always open on landing page
- 180 - Migrating VBA projects to 64-bit Excel
- 187 - VBA Case Study: TESTSUM and arrays
- 193 - VBA Case Study: DICETRAY and testing VBA code
- 194 - VBA Case Study: DICETRAY 2 and Split
Tips 201 - 300
- 215 - MsgBox and InputBox
- 217 - Games compendium
- *223 - VBA Case Study: Splitting a workbook by sheet
- *231 - VBA Case Study: Text handling and COMMAEXTRACT
- 242 - Revisiting installing macros and custom functions
- 243 - VBA Case Study: Calculating on cells of a given colour
- 248 - VBA Case Study: Ordinals (1st, 2nd, 43rd)
- 260 - VBA case study: FactorList and collections
- 263 - VBA case study: Making a hyperlinked table of contents
- 271 - VBA case study: Organising collections and fair sharing
- 277 - VBA case study: Inserting formulas into cells
Tips 301 - 400
- 306 - VBA case study: Creating filtered reports
- 307 - VBA case study: Protecting or hiding sheets
- 347 - VBA case study: Filtering visible sheets
- 359 - Audit sampling templates: VBA
- *370 - VBA case study: Making an advent calendar
- 374 - VBA case study: Reordering worksheets
- 382 - Keeping your VBA code readable
- 400 - VBA case study - Exhausting a question bank
Tips 401 - 500
- 402 - VBA case study: Capitalising titles
- *410 - Installing VBA redux
- *414 - Select Case and stamp duty revisited
- 419 - Revisiting macros and custom functions
- 454 - Revisiting splitting workbooks with VBA
- 492 - How to add a custom ribbon to your workbook part 1
- 493 - How to add a custom ribbon to your workbook part 2
- 494 - How to add a custom ribbon to your workbook part 3
Spreadsheet administration & best practice
Tips 1 - 100
- 20 - Securing workbooks
- 27 - Excel options
- 31 - Printing and print preview
- 38 - Print titles
- 39 - Comments
- 44 - Is a spreadsheet the right tool?
- 48 - Backup and version control
- 51 - Circular references
- 54 - Sharing workbooks
- 67 - Automatic vs. manual calculation
- 75 - Working with multiple screens
- 80 - Revisiting printing
- 86 - 20 Principles 1 year on
Tips 101 - 200
- 111 - Guide to naming spreadsheets
- 118 - Adding and removing passwords
- 128 - Creating a good landing page
- 130 - Customising the Ribbon and the Quick Access Toolbar
- 140 - Launch of the Spreadsheet Competency Framework
- 156 - Designing for longevity
- 157 - Printing redux
- 167 - Revisiting calculation settings
- 170 - Spell checking
- 173 - Hidden worksheets
- 184 - Revisiting circular references
- 191 - Twenty Principles for Good Spreadsheet Practice - Three years on
- 195 - Spreadsheet Competency Framework: One year on
Tips 201 - 300
- 209 - Inking
- 216 - Revisiting passwords
- 222 - Revisiting working with multiple screens
- 230 - Revisiting Excel options
- 237 - Resetting overgrown worksheets
- 240 - 6 things Google Sheets does better than Excel
- 258 - A guide to common Excel file types
- 262 - Screenshots
Tips 301 - 400
- 312 - Revisiting customising the Ribbon and Quick Access Toolbar
- 321 - Setting an Excel New Year's Resolution
- 331 - Twenty Principles for Good Spreadsheet Practice: Seven years on
- 338 - Circularity redux
- 366 - Lost coronavirus cases
- 377 - The limits of Excel
- 394 - The Accessibility Checker
- 398 - Notes vs Comments
Tips 401 - 500
- 401 - Excel on Reddit
- 404 - Mail merges
- 408 - Data structure good practices
- 409 - Controls redux
- 415 - Spreadsheet Competency Framework - Five years on
- 418 - How to review a spreadsheet
- 423 - Our Excel Christmas list
- 426 -Passwords redux
- 433 - Revisiting Excel file types
- 440 - How to get better at Excel
- 441 - Under the (Easter) Bonnet: What’s an XLSX file anyway?
- 445 - Filter/Sheet Views
- 447 - Rounding errors revisited
- 457 - Excel error checking and handling
- 460 - Revisiting hidden worksheets
- 463 - Top tips of 2022
- 466 - Setting range permissions in Excel Online
- 475 - Using ‘Show Changes’: keeping track of cell edits and the power of cloud storage
- 484 - Best of 2023
- 496 - ‘Check Performance’ in Excel
Posts by level in the Spreadsheet Competency Framework
The framework is a structure for assessing ability and proficiency when using spreadsheets.
Basic User
Tips 1 - 100
- 4 - Filters
- 5 - Quick navigation shortcuts
- 12 - Shortcuts for copying and pasting
- 19 - Paste values
- 25 - Data entry shortcuts
- 31 - Printing and print preview
- 58 - General efficiency tips
- 75 - Working with multiple screens
- 77 - Revisiting Filters
- 80 - Revisiting printing
- 83 - Revisiting navigation shortcuts
- 93 - AutoSum
- 94 - Find & Replace
Tips 101 - 200
- 108 - Revisiting Paste Special
- 129 - Hide gridlines
- 139 - Flipping the default direction
- 149 - Shortcuts: Working with rows and columns
- 157 - Printing redux
- 170 - Spell checking
- 181 - More fill options
- 186 - Quick navigation: Precedents
- 191 - Twenty Principles for Good Spreadsheet Practice - Three years on
- 192 - Hidden rows and columns
- 195 - Spreadsheet Competency Framework: One year on
- 199 - Zooming
Tips 201 - 300
- 202 - Inserting hyperlinks
- 205 - Deletion vs clearing
- 209 - Inking
- 214 - Line breaks in Excel
- 221 - AutoCorrect
- 222 - Revisiting working with multiple screens
- 227 - Making text fit
- 228 - Paste Special redux
- 229 - Alt key shortcutting
- 230 - Revisiting Excel options
- 235 - Shortcuts from a financial modeller
- 240 - 6 things Google Sheets does better than Excel
- 258 - A guide to common Excel file types
- 261 - Revisiting AutoSum
- 262 - Screenshots
- 266 - Keyboard shortcuts advent calendar
- 270 - Function key shortcuts
- 283 - Presenting text in a spreadsheet
- 286 - Revisiting grouping ranges
- 300 - Copying worksheet tabs
Tips 301 - 400
- 311 - Less-known formatting options
- 313 - Revisiting find & replace
- 321 - Setting an Excel New Year's Resolution
- 324 - Introduction to formulas and functions
- 328 - Navigation shortcuts redux
- 331 - Twenty Principles for Good Spreadsheet Practice: Seven years on
- 352 - Keyboard shortcuts for entering formulas
- 361 - How to do data entry
- 362 - The Status Bar
- 363 - Subtotal feature redux
- 366 - Lost coronavirus cases
- 373 - The 12 formulas of Christmas
- 380 - AutoSum redux
- 394 - The Accessibility Checker
- 395 - Revisiting hidden rows and columns
Tips 401 - 500
- 401 - Excel on Reddit
- 415 - Spreadsheet Competency Framework - Five years on
- 423 - Our Excel Christmas list
- 424 - Filters redux
- 433 - Revisiting Excel file types
- 440 - How to get better at Excel
- 442 - Revisiting screenshots
- 445 - Filter/Sheet Views
- 458 - Revisiting line breaks in Excel
- 466 - Setting range permissions in Excel Online
- 475 - Using ‘Show Changes’: keeping track of cell edits and the power of cloud storage
- 484 - Best of 2023
- 486 - Revisiting inserting hyperlinks
- 490 - Revisiting data entry shortcuts
General User
Tips 1 - 100
- 1 - Comparing lists
- 2 - TRIM and CLEAN
- 6 - Tables (basics)
- 7 - VLOOKUP
- 8 - Comparisons
- 10 - UPPER, LOWER, PROPER
- 11 - Formulas with fixed references
- 14 - CONCATENATE
- 18 - View shortcuts
- 24 - Remove duplicates
- 27 - Excel options
- 35 - SUM, COUNT, COUNTA, COUNTBLANK
- 38 - Print titles
- 39 - Comments
- 41 - Precision as displayed
- 42 - ROUND, ROUNDDOWN, ROUNDUP
- 44 - Is a spreadsheet the right tool?
- 45 - FLOOR, CEILING, MROUND
- 46 - ABS
- 48 - Backup and version control
- 50 - INDEX MATCH
- 52 - Quick formatting
- 57 - RAND, RANDBETWEEN
- 59 - Formatting and text formats
- 62 - Group, ungroup, subtotal
- 65 - Merge, Wrap, Autofit
- 66 - IF revisited
- 68 - MAX, MIN, LARGE, SMALL
- 70 - Revisiting CONCATENATE
- 71 - Introduction to charts
- 74 - Revisiting formulas with fixed references
- 76 - Quick charts and chart tabs
- 86 - 20 Principles 1 year on
- 88 - SUBSTITUTE
- 91 - Revisiting VLOOKUP
- 92 - Revisiting SUM, COUNT, COUNTA, COUNTBLANK
- 98 - Revisiting removing duplicates
Tips 101 - 200
- 104 - N, T
- 107 - Formula Wizard
- 110 - Grouping sheets
- 111 - Guide to naming spreadsheets
- 115 - Style Manager
- 117 - Revisiting INDEX MATCH
- 122 - Formatting using formulas
- 123 - Improving workbook viewing
- 126 - Transposing data
- 127 - AND / OR revisited
- 131 - Revisiting MAX, MIN, LARGE, SMALL
- 133 - "IS" functions
- 135 - Why merging cells sucks
- 137 - R1C1 reference style
- 140 - Launch of the Spreadsheet Competency Framework
- 150 - Adding leading zeroes
- 152 - Nesting functions in the Formula Wizard
- 160 - Revisiting the subtotal feature
- 163 - Tables redux, part 1
- 165 - FORMULATEXT
- 168 - VLOOKUP redux
- 176 - Basic statistics functions
- 200 - Fixed references redux
Tips 201 - 300
- 201 - INDEX MATCH redux
- 206 - CONVERT
- 207 - Revisiting SUBSTITUTE
- 210 - Switching bases
- 212 - Revisiting TRIM
- 213 - CODE, CHAR, UNICODE, UNICHAR
- 220 - Revisiting cell styles
- 226 - Breaking links
- 233 - Why merging cells still sucks
- 234 - Inserting a calculated value into a sentence
- 237 - Resetting overgrown worksheets
- 241 - Cell borders
- 244 - Revisiting Precision as Displayed
- 247 - Causes of formulas not working
- 251 - Cross-workbook Cell Styles
- 252 - Revisiting rounding
- 254 - Rounding with totals
- 259 - CONCATENATE redux
- 268 - Using quotation marks in formulas
- 273 - IF redux
- 275 - Cross-sheet formulas
- 279 - Revisiting N, T
- 280 - SUM and COUNT functions redux
- 282 - Spaces in formulas
- 289 - Floating point errors
- 292 - Wildcards
- 294 - Converting data stored as text
- 299 - Google Sheets: UNIQUE
Tips 301 - 400
- 301 - Google Sheets: SORT
- 303 - Revisiting the Formula Wizard
- 317 - Basic arithmetic functions
- 322 - Quartiles and percentiles
- 325 - Revisiting quick formatting
- 329 - CONCAT, TEXTJOIN
- 330 - IFS, SWITCH
- 332 - Revisiting number formatting
- 334 - Maximum and minimum functions redux
- 335 - Dealing with objects
- 337 - XLOOKUP
- 346 - Revisiting case-changing functions
- 348 - Fixing badly formatted dates
- 349 - Rounding redux
- 354 - REPLACE
- 360 - Revisiting typing special characters
- 371 - Revisiting ROW(S) and COLUMN(S) functions
- 375 - Workbook viewing redux
- 377 - The limits of Excel
- 381 - Treemap & Sunburst charts
- 387 - Revisiting fixing broken links
- 391 - Comparing the lookup functions
- 393 - AND/OR redux
- 396 - Revisiting showing formulas as text
- 398 - Notes vs Comments
Tips 401 - 500
- 404 - Mail merges
- 407 - Ranking within a group
- 411 - Revisiting Flash fill
- 412 - Working with large numbers of sheets
- 428 - Converting between statistical measures
- 429 - Revisiting cross-sheet formulas
- 430 - 3D maps
- 431 - SUBSTITUTE redux
- 436 - Trimming excess spaces redux!
- 444 - Revisiting XLOOKUP
- 446 - Desktop vs Online
- 447 - Rounding errors revisited
- 449 - Let LET simplify formulas
- 453 - Revisiting audit sampling with dynamic arrays
- 455 - Data conversion
- 461 - Things Google Sheets (still) does better than Excel
- 463 - Top tips of 2022
- 481 - Refreshing cell styles
- 483 - Presenting tabular Excel data in PowerPoint
- 496 - ‘Check Performance’ in Excel
Creator
Tips 1 - 100
- 3 - Introduction to conditional formatting
- 9 - Dropdowns
- 13 - Tables (further learning)
- 15 - AND, OR
- 16 - Data validation
- 17 - IF and nested IFs
- 20 - Securing workbooks
- 21 - LEFT, RIGHT, MID
- 23 - Evaluate Formula and formula auditing
- 26 - LEN, FIND, SEARCH
- 29 - PivotTables (basics)
- 30 - Reviewing formulas
- 32 - Naming cells
- 36 - PivotTables (further learning)
- 37 - Creating error checks
- 40 - Goal Seek
- 43 - SUMIF, COUNTIF, SUMIFS, COUNTIFS
- 47 - NETWORKDAYS, WORKDAY
- 49 - Working with dates
- 51 - Circular references
- 53 - Causes of formulas not calculating
- 55 - Finding a function that fits
- 56 - SUMPRODUCT
- 60 - DATE, EDATE, EOMONTH
- 61 - TODAY, NOW
- 63 - ROW, COLUMN, ROWS, COLUMNS
- 64 - Revisiting PivotTables
- 69 - HYPERLINK
- 72 - Copy and paste errors
- 73 - Revisiting conditional formatting
- 78 - Revisiting COUNTIF, SUMIF, COUNTIFS, SUMIFS
- 79 - Revisiting Tables
- 85 - NPV, XNPV, IRR, XIRR
- 87 - Sparklines
- 89 - NETWORKDAYS.INTL, WORKDAY.INTL
- 90 - DATEDIF
- 95 - Revisiting naming cells and the Name Manager
- 96 - CHOOSE
Tips 101 - 200
- 102 - Go To & Go To Special
- 103 - Loan-related functions
- 105 - Revisiting Text to Columns
- 106 - CELL, INFO
- 112 - OFFSET
- 113 - Revisiting Goal Seek
- 114 - Revisiting data validation & creating dropdown menus
- 118 - Adding and removing passwords
- 120 - Revisiting comparing lists
- 124 - Flash fill
- 125 - Too many cell formats
- 128 - Creating a good landing page
- 130 - Customising the Ribbon and the Quick Access Toolbar
- 136 - Quick formatting for formulas, inputs, and blanks
- 145 - RANK.AVG, RANK.EQ
- 146 - Revisiting RAND, RANDBETWEEN
- 148 - Advanced Filter
- 151 - SUBTOTAL, AGGREGATE
- 153 - Conditional formatting: Layering multiple formats
- 154 - Database functions
- 155 - Using INDEX MATCH with multiple variables
- 158 - Revisiting formulas that aren't calculating
- 159 - Setting permissions for different ranges
- 161 - Revisiting text extraction functions
- 162 - Normal distribution functions
- 164 - Tables redux, part 2
- 171 - Using #N/A in charts
- 172 - Rotating between values
- 173 - Hidden worksheets
- 177 - Formula-driven data validation
- 179 - GETPIVOTDATA
- 184 - Revisiting circular references
- 185 - Revisiting sparklines
- 188 - Named cells redux
- 189 - Revisiting CHOOSE
- 190 - PivotTables redux
- 196 - Splitting data by category
- 197 - WEEKDAY
- 198 - Checking formulas in the formula bar
Tips 201 - 300
- 208 - Revisiting TODAY, NOW
- 211 - Sorting with formulas
- 216 - Revisiting passwords
- 219 - Nested dropdowns
- 224 - Revisiting SUMPRODUCT
- 232 - Analysing frequency tables
- 236 - Creating numeric patterns
- 238 - Revisiting working with dates
- 239 - How to standardise data
- 245 - Finding the last number in a range
- 246 - Modelling a simple loan
- 249 - Revisiting making dates with formulas
- 250 - Goal Seek redux
- 253 - Calculating the effective interest rate
- 255 - Power Query: Combining multiple tables
- 256 - Power Query: Three-way matching
- 264 - Working day functions revisited
- 265 - Revisiting DATEDIF
- 267 - Creating dynamic ranges
- 272 - Revisiting finding the function you need
- 274 - Mixing multiple chart types
- 276 - Conditional formatting redux
- 281 - Revisiting CELL and INFO
- 284 - Revisiting Go To and Go To Special
- 285 - FIFO and LIFO templates
- 287 - Revisiting Advanced Filter
- 288 - Revisiting database functions
- 290 - Sparklines redux
- 291 - Making a set of PivotTables
- 293 - Revisiting layering conditional formats
- 295 - Binomial distribution functions
- 296 - Data validation redux
- 297 - Revisiting using #N/A! in charts
- 298 - Formula issues redux
Tips 301 - 400
- 302 - Linear regression functions
- 304 - Google Sheets: FILTER
- 305 - Revisiting counting distinct items
- 308 - Google Sheets: QUERY
- 309 - Randomisation redux
- 310 - Revisiting reviewing formulas
- 312 - Revisiting customising the Ribbon and Quick Access Toolbar
- 315 - Google Sheets: Counting distinct items
- 316 - Conditional summary functions redux
- 320 - Revisiting OFFSET
- 323 - Creating a trial balance from accounting data
- 326 - Revisiting loan functions
- 327 - Introduction to dynamic array functions
- 333 - Revisiting GETPIVOTDATA
- 336 - Why is there an @ in my Excel formula?
- 338 - Circularity redux
- 339 - Power Query: Filtering for a date range
- 341 - Text extraction functions redux
- 342 - Revisiting HYPERLINK
- 343 - SUMPRODUCT redux
- 350 - Power Query: Creating a ranking
- 351 - Non-linear regression
- 353 - Audit sampling templates: Dynamic arrays
- 355 - Connecting Google Sheets and Excel
- 356 - Audit sampling templates: Traditional formulas
- 364 - Dynamic arrays: Creating a visual work schedule
- 367 - PivotCharts
- 368 - Power Pivot: Connections
- 376 - Discounting functions redux
- 378 - Revisiting splitting data by category
- 379 - Processing survey results
- 383 - Revisiting ranking functions
- 384 - Data types
- 385 - Power Query: Creating transaction references
- 389 - Power Query Dropping Rows and Columns
- 390 - Advanced uses of INDEX
- 392 - Text to Columns redux
- 397 - Power Query - Looking up charge-out rates
- 399 - Power pivot - KPIs
Tips 401 - 500
- 403 - Power Query: Merge Kinds
- 405 - DATEDIF redux
- 406 - Power Pivot: Filtering active contracts
- 408 - Data structure good practices
- 413 - Flattening and unfolding data
- 416 - PivotTable layout
- 417 - Custom data types
- 418 - How to review a spreadsheet
- 420 - STOCKHISTORY and exchange rates
- 421 - Comparing lists redux
- 422 - Better error catching formulas
- 425 - Revisiting unpivoting
- 426 - Passwords redux
- 427 - Power Query: Word frequency analysis
- 432 - Date-creating formulas redux
- 434 - Revisiting making sets of PivotTables
- 435 - Working with times
- 437 - Revisiting dynamic arrays
- 443 - Next level FILTERing (the FILTER function)
- 448 - A distinct count conundrum
- 450 - Revisiting Custom Data Validation
- 451 - Sparklines refreshed
- 452 - Named Ranges refreshed
- 457 - Excel error checking and handling
- 459 - Examples of using LAMBDA() in Excel
- 462 - New text manipulation functions - TEXTBEFORE and TEXTAFTER
- 471 – Refreshing GETPIVOTDATA
- 474 - How to REDUCE repetitive calculations to a single step
- 476 - Cross join in Excel using a formula
- 477 - Introduction to creating map charts in Excel
- 479 - Advanced Filter redux
- 485 - Refreshing NETWORKDAYS and WORKDAY functions
- 488 - The basics of charts in Excel
- 489 – The basics of charts in Excel part 2: Combo charts
- 491 - Creating a dynamic lead schedule
Developer
Tips 1 - 100
- 22 - Data tables
- 28 - Text to columns
- 33 - Introduction to macros
- 34 - ADDRESS, INDIRECT
- 54 - Sharing workbooks
- 67 - Automatic vs. manual calculation
- 81 - How to install macros and custom functions
- 82 - Useful macros and custom functions
- 84 - Scenario Manager
- 97 - Working with imported data
- 99 - Introduction to array formulas
- 100 - Working with arrays
Tips 101 - 200
- 101 - Form controls
- 109 - Selecting a scenario
- 116 - Solver Add-In
- 119 - Revisiting Data Tables
- 121 - Further dropdown menu approaches
- 132 - Revisiting ADDRESS, INDIRECT
- 134 - Making Benford's Law in Excel
- 138 - When will I get paid?
- 141 - VBA case study - SDLT
- 142 - How many distinct items?
- 143 - Conditional formatting: Colouring a whole row
- 144 - VBA case study - Factor and loops
- 147 - VBA case study - StringConcat and 'For Each'
- 156 - Designing for longevity
- 166 - VBA Case Study: Creating scenarios
- 167 - Revisiting calculation settings
- 169 - Revisiting NPV, XNPV, IRR, XIRR
- 174 - VBA Case Study: Timestamps
- 175 - The Excel Data Model
- 178 - VBA Case Study: Always open on landing page
- 180 - Migrating VBA projects to 64-bit Excel
- 182 - Revisiting array formulas
- 183 - FREQUENCY
- 187 - VBA Case Study: TESTSUM and arrays
- 193 - VBA Case Study: DICETRAY and testing VBA code
- 194 - VBA Case Study: DICETRAY 2 and Split
Tips 201 - 300
- 203 - Consolidate
- 204 - Count items in common
- 215 - MsgBox and InputBox
- 217 - Games compendium
- 218 - Data tables redux
- 223 - VBA Case Study: Splitting a workbook by sheet
- 225 - Finding items that add to a target
- 231 - VBA Case Study: Text handling and COMMAEXTRACT
- 242 - Revisiting installing macros and custom functions
-
243 - VBA Case Study: Calculating on cells of a given colour
- 248 - VBA Case Study: Ordinals (1st, 2nd, 43rd)
- 257 - Revisiting the Solver Add-In
- 260 - VBA case study: FactorList and collections
-
263 - VBA case study: Making a hyperlinked table of contents
- 269 - Revisiting form controls
- 271 - VBA case study: Organising collections and fair sharing
- 277 - VBA case study: Inserting formulas into cells
- 278 - Using data tables for Monte Carlo simulations
Tips 301 - 400
- 306 - VBA case study: Creating filtered reports
- 307 - VBA case study: Protecting or hiding sheets
- 314 - ADDRESS and INDIRECT redux
- 319 - Revisiting selecting a scenario
- 340 - Calculating tiered percentages
- 344 - Power Query: Getting started with web queries
- 345 - Spilling errors with dynamic arrays
- 347 - VBA case study: Filtering visible sheets
- 359 - Audit sampling templates: VBA
- 365 - Revisiting finding items that add to a target
- 369 - Power Pivot: Measures
- 370 - VBA case study: Making an advent calendar
- 374 - VBA case study: Reordering worksheets
- 382 - keeping your vba code readable
- 386 - Power Pivot - RELATEDTABLE
- 388 - Power Pivot: Filtering measures
- 400 - VBA case study - Exhausting a question bank
Tips 401 - 500
- 402 - VBA case study: Capitalising titles
- 409 - Controls redux
- 410 - Installing VBA redux
- 414 - Select Case and stamp duty revisited
- 419 - Revisiting macros and custom functions
- 438 - Power Query: Creating custom functions
- 439 - Power Pivot: Create a running total
- 441 - Under the (Easter) Bonnet: What’s an XLSX file anyway?
- 454 - Revisiting splitting workbooks with VBA
- 456 - Revisiting loan modelling
- 460 - Revisiting hidden worksheets
- 478 - Introduction to Power Automate
- 492 - How to add a custom ribbon to your workbook part 1
- 493 - How to add a custom ribbon to your workbook part 2
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.